import pandas as pd
from fastcache import lru_cache
from hctiming.utils import inds
from hctiming.utils import path
from hctiming.utils import Tdatabase
from hctiming.utils.Tdatabase import rds,windengine
import os
from .path import strategy_PATH
from .path import DATA_PATH
import json


class TimingBase(object):
    def __init__(self, name=None, description=None, category=None, index=inds, database='timing'):
        self.index = inds
        # self.version = version
        self.name = name
        self.description = description
        self.database = database
        # 查看当前策略
        tables = pd.read_sql('SHOW TABLES',rds,index_col='Tables_in_timing')
        self.strategy = [f for f in os.listdir(strategy_PATH) if f.endswith('.py')]

        # 创建数据库表
        if 'strategy_info' not in tables.index:
            create_info_sql = """
                           CREATE TABLE IF NOT EXISTS strategy_info
                           (
                               fid           INT            NOT NULL AUTO_INCREMENT COMMENT 'factor id',
                               name          VARCHAR(40)    NOT NULL UNIQUE COMMENT 'factor name',
                               description   TEXT           NOT NULL COMMENT 'factor description',
                               category      INT            NOT NULL COMMENT 'factor category',
                               update_time   DATETIME       NOT NULL COMMENT 'factor update time',
                               PRIMARY KEY (fid)
                           );
                           """
            rds.execute(create_info_sql)

        # 如果策略信息不存在，则插入一条记录
        check_info_sql = """
            SELECT name FROM strategy_info
            WHERE name = '{0}'""".format(name)
        info = pd.read_sql(check_info_sql, rds)
        if info.empty:
            update_time = pd.Timestamp.now().strftime('%Y%m%d')
            insert_info_sql = """INSERT INTO strategy_info(name, description, category, update_time)
              VALUES("{0}", "{1}", {2}, '{3}')""".format(name, description, category,update_time)
            rds.execute(insert_info_sql)

        # 如果策略结果不存在，则建立策略表
        if name in tables.index:
            pass
        else:
            create_strategy_sql = """
                CREATE TABLE IF NOT EXISTS {0}
                 (
                  `index` bigint(20) NULL DEFAULT NULL,
                  `ind` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
                  `name` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
                  `point` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
                  `start` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
                  `endure` double NULL DEFAULT NULL,
                  `updatetime` datetime(0) NULL DEFAULT NULL,
                  `criteria` double NULL DEFAULT NULL,
                  `results` double NULL DEFAULT NULL,
                  `pct` double NULL DEFAULT NULL,
                  `class` bigint(20) NULL DEFAULT NULL,
                  INDEX `ix_mytimestat_index`(`index`) USING BTREE
                )
                """.format(self.name)
            rds.execute(create_strategy_sql)

        FOLDER_PATH = os.path.join(DATA_PATH, '{0}'.format(name))
        if not os.path.exists(FOLDER_PATH):
            os.makedirs(FOLDER_PATH)

    def cal_signal(self):
        # 计算信号值
        pass

    def update(self):
        [data, mydict] = self.cal_signal()
        # 检查返回数据
        assert {'ind','name','point','start','endure','updatetime','criteria','results','pct'}.issubset(data.columns)
        # data.dropna(inplace=True)  # 要特别留意下dropna 有些因子本来就是nan 到底该不该drop需要考虑！
        data['update_time'] = pd.Timestamp.now()

        # 先删除在插入
        try:
            rds.execute('DROP TABLE `{0}`.`{1}`'.format('timing', self.name))  # 复制表结构
        except:
            pass

        # 插入数据
        data.to_sql('{0}'.format(self.name), rds)








